package com.atguigu.gmallsugar.mapper;

import com.atguigu.gmallsugar.bean.OrderRefund;
import com.atguigu.gmallsugar.bean.Province;
import com.atguigu.gmallsugar.bean.Spu;
import com.atguigu.gmallsugar.bean.Tm;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;

/**
 * @Author lzc
 * @Date 2023/8/14 14:38
 */
@Mapper
public interface TradeMapper {
    @Select("select " +
        "sum(order_amount) order_amount " +
        "from dws_trade_sku_order_window " +
        "partition (par#{date})")
    BigDecimal gmv(Integer date);  // #: par1 par'1'  $:par1
    
    @Select("select " +
        "spu_name, " +
        "sum(order_amount) order_amount " +
        "from dws_trade_sku_order_window " +
        "partition (par#{date}) " +
        "group by spu_name")
    List<Spu> gmvBySpu(@Param("date") Integer date);
    
    @Select("select " +
        "trademark_name, " +
        "sum(order_amount) order_amount " +
        "from dws_trade_sku_order_window " +
        "partition(par#{date}) " +
        "group by trademark_name;")
    List<Tm> gmvByTm(@Param("date") Integer date);
    
    @Select("select \n" +
        "    if(t1.category3_name is not null, t1.category3_name, t2.category3_name) category3_name,\n" +
        "    if(t1.order_amount is not null, t1.order_amount, 0) order_amount,\n" +
        "    if(t2.refund_amount is not null, t2.refund_amount, 0) refund_amount,\n" +
        "    if(t2.refund_count is not null, t2.refund_count, 0) refund_count,\n" +
        "    if(t2.refund_user_count is not null, t2.refund_user_count, 0) refund_user_count\n" +
        "from \n" +
        "(select \n" +
        "    category3_name, \n" +
        "    sum(order_amount) order_amount \n" +
        "from dws_trade_sku_order_window\n" +
        "partition(par#{date})\n" +
        "group by category3_name) t1\n" +
        "full join\n" +
        "(select \n" +
        "    category3_name,\n" +
        "    sum(refund_amount) refund_amount,\n" +
        "    sum(refund_count) refund_count,\n" +
        "    count(distinct user_id) refund_user_count\n" +
        "from dws_trade_trademark_category_user_refund_window\n" +
        "partition(par#{date})\n" +
        "group by category3_name) t2\n" +
        "on t1.category3_name=t2.category3_name")
    List<OrderRefund> category3Stat(@Param("date") Integer date);
    
    
    @Select("select province_name, " +
        "sum(order_amount) order_amount, " +
        "sum(order_count) order_count " +
        "from dws_trade_province_order_window  " +
        "partition(par#{date}) " +
        "group by province_name")
    List<Province> province(@Param("date") Integer date);
    
}
